package dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.liudecai.utils.common.CommonUtils;
import com.liudecai.utils.jdbc.TxQueryRunner;

import bean.StudentAndTeacher;
import bean.Teacher;

public class StuAndTeacherDao {
	
	public StudentAndTeacher FindById(int id) throws SQLException {
		QueryRunner qr = new TxQueryRunner();
		String sql = "select * from student s,teacher t where s.t_num=t.id and s.id=?";
		
		/*
		 * 这种方法不好：
		 * 	执行了两次查询，效率低浪费资源
		 */
		StudentAndTeacher studentAndTeacher = qr.query(sql, new BeanHandler<StudentAndTeacher>(StudentAndTeacher.class),id);
		Teacher teacher = qr.query(sql, new BeanHandler<Teacher>(Teacher.class),id);
		studentAndTeacher.setTeacher(teacher);
		return studentAndTeacher;
	}
	
	/**
	 * 这种方法只查询了一次
	 * MapHandler
	 * 	把结果集转换成一个Map
	 * 	以列名为key，以列值为值
	 * 	id:1
	 *  s_name:刘德财
	 *  .....
	 *  相同的列名会覆盖
	 */
	public StudentAndTeacher FindById2(int id) throws SQLException {
		QueryRunner qr = new TxQueryRunner();
		String sql = "select * from student s,teacher t where s.t_num=t.id and s.id=?";
		// MapHandler 把结果集转换成一个Map
		@SuppressWarnings("rawtypes")
		Map map = qr.query(sql, new MapHandler(), id);
		
//		System.out.println(map);
//		return null;
		
		/*
		 * 利用ToBean将map转成两个对象
		 */
		StudentAndTeacher sat = CommonUtils.MapToBean(map, StudentAndTeacher.class);
		Teacher teacher = CommonUtils.MapToBean(map, Teacher.class);
		sat.setTeacher(teacher); 	// 两个对象建立关系
		return sat;
	}
	
	public List<StudentAndTeacher> FindAll() throws SQLException {
		QueryRunner qr = new TxQueryRunner();
		String sql = "select * from student s,teacher t where s.t_num=t.id";
		// MapHandler 把结果集转换成Map
		List<Map<String, Object>> maplist = qr.query(sql, new MapListHandler());
		// 用来装载生成的对象
		List<StudentAndTeacher> satlist = new ArrayList<StudentAndTeacher>();
		for(Map<String, Object> map : maplist) {
			/*
			 * 把每个map映射成两个对象
			 * 再给他们建立关系
			 * 添加到satlist中
			 */
			// 把map转成StudentAndTeacher对象
			StudentAndTeacher st = CommonUtils.MapToBean(map, StudentAndTeacher.class);
			// 把map转成Teacher对象
			Teacher teacher = CommonUtils.MapToBean(map, Teacher.class);
			// 两个对象建立关系
			st.setTeacher(teacher);
			satlist.add(st);
		}
		return satlist;
	}
	
	/**
	 * 获取所有学生姓名(单列查询)
	 * 	使用ColumnListHandler
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<Object> FindSutNames() throws SQLException {
		QueryRunner qr = new TxQueryRunner();
		String sql = "select s_name from student";
		return (List<Object>) qr.query(sql, new ColumnListHandler());
	}
	
	/**
	 * 返回列的总记录数
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public long count() throws SQLException {
		QueryRunner qr = new TxQueryRunner();
		String sql = "select count(*) from student";
		return (long) qr.query(sql, new ScalarHandler());
	}
	
	
	
}
